// ================================================================================
// 		File: PartyQuestionnaire.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-06-27 21:47
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq; 
using System.Text;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Questionnaire
{
    /// <summary>
    /// 调查问卷
    /// </summary>   
    public class PartyQuestionnaire 
    { 
		#region 属性
        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }
		
        /// <summary>
        /// 标题
        /// </summary>
        public string Title { get; set; }
		
        /// <summary>
        /// 开始时间
        /// </summary>
        public DateTime? BeginTime { get; set; }
		
        /// <summary>
        /// 结束时间
        /// </summary>
        public DateTime? EndTime { get; set; }
		
        /// <summary>
        /// 1草稿 4发布
        /// </summary>
        public int Stauts { get; set; }
		
        /// <summary>
        /// 调查对象1、党员 2、群众（注册用户）3、所有
        /// </summary>
        public string TestObject { get; set; }
		
        /// <summary>
        /// 模块
        /// </summary>
        public string Module { get; set; }
		
        /// <summary>
        /// 创建用户
        /// </summary>
        public string CreateBy { get; set; }
		
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }
		
        /// <summary>
        /// 修改用户
        /// </summary>
        public string ModifyBy { get; set; }
		
        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }
		
		#endregion 

		#region 查询管理

		 public static DataSet GetGridData(int pageIndex, int pageSize, string status ,string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.ID,data.Title,data.BeginTime,data.EndTime,
data.Stauts,data.TestObject,data.Module,data.CreateBy,data.CreateTime,data.ModifyBy,data.ModifyTime
                         ,case data.Stauts when 4 then '已发布' else '草稿' end as  StatusName,
 case data.TestObject when '1' then '党员' when '2' then '注册用户' else '党员、注册用户'  end as  TestObjectName
from tblPartyQuestionnaire data
                        where 1=1 ");
            if (!string.IsNullOrEmpty(searchKey))
            {
                sb.Append(" and data.Title like ?key ");
                parameters.Add(
                    new MySqlParameter() { DbType = DbType.String, ParameterName = "key", Value = string.Format("%{0}%", searchKey) });
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and data.Stauts=?Stauts");
                parameters.Add(new MySqlParameter() { DbType = DbType.Int32, ParameterName = "Stauts", Value = status });
            }
            sb.Append(" order by data.begintime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取题
        /// </summary>
        /// <returns></returns>
        public static DataSet GetPartyQuestionnaireSubjectTest(string id)
        {
            string sql = @"CREATE TEMPORARY TABLE tempT select d.*,
case d.Category when 1 then 'checkbox' else 'radio' end as CategoryName 
from tblPartyQuestionnaireSubject d
                where d.Questionnaireid=?Questionnaireid and Enabled=1
ORDER BY orderno ;
select * from tempT;
select s.* from tblPartyQuestionnaireOption s
where EXISTS (select ID from tempT where tempT.ID=s.SubjectID ) order by Orderno;

drop table tempT;";
            MySqlParameter[] para = new MySqlParameter[] {
                new MySqlParameter() {
                DbType =DbType.String, ParameterName= "Questionnaireid",Value= id }
            };
            return MySqlHelper.ExecuteDataSet(sql, para);
        }


      /// <summary>
      /// 统计数据
      /// </summary>
      /// <returns></returns>
        public static void QuestionnaireStatistics(string questionnaireID)
        {
            MySqlParameter[] para = new MySqlParameter[] {
                new MySqlParameter() {
                DbType =DbType.String, ParameterName= "questionnaireID",Value= questionnaireID }
            };
            var sql = string.Format(@"CALL sp_questionnaire_statistics(?questionnaireID); ");
            MySqlHelper.ExecuteNonQuery(sql, para);

        }

        public static DataTable GetListByLoginID(string loginId, string runsate)
        {
            var testObject = runsate == "0" ? "1" : "2";
            MySqlParameter[] para = new MySqlParameter[]
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "loginID",
                    Value = loginId
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "testObject",
                    Value = testObject
                }
            };
            var sql = @"select q.*  from tblpartyquestionnaire q
where q.Stauts=4 and q.BeginTime<NOW() and q.EndTime >NOW() and (q.TestObject='3' or q.TestObject=?testObject ) and
not EXISTS (select ID from tblpartyquestionnairerecord r where r.QuestionnaireID=q.ID and r.CreateBy=?loginID ); ";
            return MySqlHelper.ExecuteDataTable(sql, para);

        }
        public static DataSet GetListByLoginID(int pageIndex, int pageSize, string loginId, string runsate)
        {
            var testObject = runsate == "0" ? "1" : "2";
            MySqlParameter[] para = new MySqlParameter[]
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "loginID",
                    Value = loginId
                },
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "testObject",
                    Value = testObject
                },
                 new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };
            var sql = @"select SQL_CALC_FOUND_ROWS q.*  from tblpartyquestionnaire q
where q.Stauts=4 and q.BeginTime<NOW() and q.EndTime >NOW() and (q.TestObject='3' or q.TestObject=?testObject ) and
not EXISTS (select ID from tblpartyquestionnairerecord r where r.QuestionnaireID=q.ID and r.CreateBy=?loginID ) ";
            sql+=(" order by  CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sql, para);

        }
        #endregion
    }
}
